Jan 13

Written by: QCTO Blog
Wednesday, January 13, 2010 

Basically, this can be achieved by two different means with QCTO, described in the following.

"C:\Program Files\Quest Software\Quest Code Tester for

Oracle\QuestCodeTesterOracle.exe" /u=qcto /p=qcto /d=test /S="My_Test_Suite" /r /Close

Each of these is available in three different variants:

• Simple overall status is returned in an output parameter.
• Simple overall status is returned in an output parameter and another output parameter contains a ref cursor, through which you can get details of the test cases that were executed.
• Simple overall status is returned in an output parameter and another output parameter contains a nested table, through which you can get details of the test cases that were executed.

The usage of these is described in detail in the QCTO on-line help.

declare
   l_result varchar2(32767);
begin
   qu_test.run_suite_by_name('My_Test_Suite', l_result);
   dbms_output.put_line('l_result = ''' || l_result || '''');
end;
/
l_result = 'RUNTIME-ERROR'

PL/SQL procedure successfully completed.

The usage of these is described in detail in the QCTO on-line help.

The package contains two main functions, described in the following.

The function basically does the following:

• Finds test name/GUID through the GUID/name provided for the test definition.
• Builds a list of GUIDs for the test cases that are to be run. This is resolved through the third parameter.
• Calls RUN_TEST_FOR4.
• Show the detailed results on DBMS_OUTPUT and count the number of test cases that failed.
• Returns number of failed test cases.

COMMA_TO_TABLE

create or replace package qcto_ci as
/**
 * Quest Code Tester for Oracle Continuous Integration interface.
 */

  function run_test_suite(
    p_program_owner in varchar2,
    p_test_suite in varchar2,
    p_test_cases in varchar2 := null
  )
  return integer;
  function run_test_definition(
    p_program_owner in varchar2,
    p_test_definition in varchar2,
    p_test_cases in varchar2 := null,
    p_indent in pls_integer := 0
  )
  return integer;

  function comma_to_table(p_s in varchar2)
  return dbms_utility.uncl_array;
  function table_to_comma(p_t in dbms_utility.uncl_array)
  return varchar2;
end qcto_ci;
/

create or replace package body qcto_ci as
  c_delimiter constant char(1) := ',';

/**
 * Runs given Quest Code Tester for Oracle test suite. Shows details about each
 * test case on DBMS_OUTPUT.
 * @param   p_program_owner
 *                  Owner of program under test.
 * @param   p_test_suite
 *                  QCTO test suite name or GUID.
 * @param   p_test_cases
 *                  Comma-separated list of LIKE expressions for test case names
 *                  or GUIDs for the test cases to run. LIKE is only supported
 *                  with the names, not the GUIDs as that doesn't make sense.
 *                  Default NULL, which means all test cases.
 * @return  Number of test cases that failed during the execution.
 */

  function run_test_suite(
    p_program_owner in varchar2,
    p_test_suite in varchar2,
    p_test_cases in varchar2 := null
  )
  return integer as

    l_failures integer := 0;

  begin
    for s in (
          select qs.name suite_name,
                 qs.universal_id
          from   qu_suite qs
          where  (
                      qs.universal_id = p_test_suite
                   or qs.name = p_test_suite
                 )
        ) loop
      dbms_output.put_line('SUITE ' || s.suite_name);

      for td in (
            select qsh.harness_guid,
                   qh.program_name test_definition_name
            from   qu_suite_harness qsh,
                   qu_harness qh
            where  qsh.suite_guid = s.universal_id
            and    qh.universal_id = qsh.harness_guid
          ) loop
        l_failures := l_failures + run_test_definition(
          p_program_owner,
          td.harness_guid /*td.test_definition_name*/,
          p_test_cases,
          1
        );
      end loop;

      dbms_output.put_line(
        'SUITE ' || s.suite_name || ': ' ||
        l_failures || ' test case(s) failed'
      );
    end loop;

    return l_failures;
  end run_test_suite;

/**
 * Runs given Quest Code Tester for Oracle test definition. Shows details about
 * each test case on DBMS_OUTPUT. DBMS_OUTPUT is disabled while the test is
 * running.
 * @param   p_program_owner
 *                  Owner of program under test.
 * @param   p_test_definition
 *                  QCTO test definition name or GUID.
 * @param   p_test_cases
 *                  Comma-separated list of LIKE expressions for test case names
 *                  or GUIDs for the test cases to run. LIKE is only supported
 *                  with the names, not the GUIDs as that doesn't make sense.
 *                  Default NULL, which means all test cases.
 * @return  Number of test cases that failed during the execution.
 */

  function run_test_definition(
    p_program_owner in varchar2,
    p_test_definition in varchar2,
    p_test_cases in varchar2 := null,
    p_indent in pls_integer := 0
  )
  return integer as

    l_result               varchar2(100);
    l_results              qu_result_xp.last_run_results_api_nt;
    l_failures             integer := 0;
    l_test_name            varchar2(32767);
    l_test_cases_csv       varchar2(32767);
    l_test_cases_array     dbms_utility.uncl_array;
    l_test_case_guid_array dbms_utility.uncl_array;
    l_test_case_guid_csv   varchar2(32767);
    l_test_guid            varchar2(100);
    l_n                    binary_integer;

  begin
    if p_test_cases is not null then
      -- Expand test case name filters by their GUIDs.

      if p_test_definition like '{%}' then
        l_test_guid := p_test_definition;

        -- Find test name through GUID provided.
        select program_name
        into   l_test_name
        from   qu_harness qh
        where  qh.universal_id = p_test_definition;
      else
        l_test_name := p_test_definition;

        -- Find test GUID through name provided.
        select qh.universal_id
        into   l_test_guid
        from   qu_harness qh
        where  qh.program_name = p_test_definition;
      end if;

      -- Make sure all test cases are GUIDs.
      l_test_cases_array := comma_to_table(p_test_cases);
      for i in 
          nvl(l_test_cases_array.first, 0) .. 
          nvl(l_test_cases_array.last, -1) loop
        if l_test_cases_array(i) not like '{%}' then
          for tc in (
                select qtc.universal_id
                from   qu_unit_test qut,
                       qu_test_case qtc
                where  qut.harness_guid = l_test_guid
                and    qtc.unit_test_guid = qut.universal_id
                and    qtc.name like l_test_cases_array(i)
              ) loop
            l_test_case_guid_array(l_test_case_guid_array.count + 1) :=
                tc.universal_id;
          end loop;
        else
          l_test_case_guid_array(l_test_case_guid_array.count + 1) :=
              l_test_cases_array(i);
        end if;
      end loop;
      l_test_case_guid_csv := table_to_comma(l_test_case_guid_array);
    end if;

    if p_test_cases is null or
        p_test_cases is not null and l_test_case_guid_array.count > 0 then
      -- Only run if we have been asked to run all test cases or if we have been
      -- asked to run specific test cases and we found them.
      qu_test.run_test_for4(
        p_program_owner,
        l_test_name,
        l_result,
        l_results,
        null,
        l_test_case_guid_csv,
        ',',
        null
      );
    end if;

    if l_results is not null then
      for i in nvl(l_results.first, 0) .. nvl(l_results.last, -1) loop
        if l_results(i).result_level not in ('INPUT', 'OUTPUT') and
            l_results(i).result_status is not null then
          -- Don't show input/output parameters but show the rest if they have been
          -- executed (RESULT_STATUS not NULL).

          dbms_output.put(
            rpad (' ', (p_indent + l_results(i).h_level - 1) * 2) ||
            l_results(i).result_level || ' ' ||
            l_results(i).name ||
            ': '
          );

          if l_results(i).result_level in ('UNITTEST', 'TESTCASE') then
            -- Only show start/stop for tests and test cases.
            dbms_output.put(
              'Started: ' || 
                  to_char(l_results(i).start_on, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
              'Stopped: ' || 
                  to_char(l_results(i).end_on, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
              '(' || round((l_results(i).end_on - l_results(i).start_on) * 86400) || 
                  's) '
            );
          end if;

          dbms_output.put(l_results(i).result_status);
          if l_results(i).result_status != 'SUCCESS' then
            dbms_output.put(': ' || l_results(i).description);
          end if;
          dbms_output.put_line('');

          if l_results(i).result_level = 'TESTCASE' and
              l_results(i).result_status != 'SUCCESS' then
            l_failures := l_failures + 1;
          end if;
        end if;
      end loop;
    end if;

    dbms_output.put_line(
      rpad(' ', p_indent * 2) || 'HARNESS ' ||
      p_program_owner || '.' || l_test_name || ': ' ||
      l_failures || ' test case(s) failed'
    );

    return l_failures;
  end run_test_definition;

/**
 * Converts comma-separated values to an array containing the values.
 * DBMS_UTILITY.COMMA_TO_TABLE does not work with values that are not legal
 * SQL identifiers.
 * @param   p_s     Comma-separated values.
 * @return  Array of values.
 */

  function comma_to_table(p_s in varchar2)
  return dbms_utility.uncl_array as

    i pls_integer;
    j pls_integer;
    l pls_integer;
    l_result dbms_utility.uncl_array;

  begin
    i := 1;
    j := instr(p_s, c_delimiter, i, 1);
    if j = 0 then
      j := l + 1;
    end if;
    l := nvl(length(p_s), 0);
    while i <= l loop
      l_result(l_result.count + 1) := substr(p_s, i, j - i);
      i := j + 1;
      j := instr(p_s, c_delimiter, i, 1);
      if j = 0 then
        j := l + 1;
      end if;
    end loop;

    return l_result;
  end comma_to_table;

/**
 * Converts array elements to comma-separated values.
 * DBMS_UTILITY.TABLE_TO_COMMA does not work with values that are not legal SQL
 * identifiers.
 * @param   p_t     Array of values.
 * @return  Comma-separated values.
 */

  function table_to_comma(p_t in dbms_utility.uncl_array)
  return varchar2 as

    l_result varchar2(32767);

  begin
    for i in nvl(p_t.first, 0) .. nvl(p_t.last, -1) loop
      l_result := l_result || p_t(i) || c_delimiter;
    end loop;

    return rtrim(l_result, c_delimiter);
  end table_to_comma;
end qcto_ci;
/

set serveroutput on format truncated
set scan off
set define &
set scan on
set verify off
set feedback off

variable l_failures number

begin
  :l_failures := 1;
end;
/

begin
  :l_failures := qcto_ci.run_test_definition('&&1', '&&2', '&&3');
end;
/

exit :l_failures

call sqlplus -s <user>/<password>@<tns> @run_qcto_test_definition.sql "%1" "%2" "%3"
exit %ERRORLEVEL%

run_qcto_test_definition.bat SCOTT TEST_DATA_TYPES "%%BLOB%%,%%CLOB%%,%%BOOLEAN%%"

set serveroutput on format truncated

begin
  dbms_output.put_line(
    qcto_ci.run_test_definition(
      'SCOTT',
      'TEST_DATA_TYPES',
      '%BLOB%,%CLOB%,%BOOLEAN%'
    )
  );
end;
/
HARNESS SCOTT.TEST_DATA_TYPES: FAILURE: Test failed at this level.
 UNITTEST TEST_BLOB: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
    TESTCASE TEST_BLOB S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
      OUTCOME Scalar is equal to the expected value?: SUCCESS
      OUTCOME Value modified by the program is NULL?: SUCCESS
 UNITTEST TEST_BOOLEAN: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) FAILURE: Test failed at this level.
    TESTCASE TEST_BOOLEAN S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
      OUTCOME Does the Boolean expression evaluate to FALSE?: SUCCESS
      OUTARG B2: SUCCESS
      OUTARG [Function return value]: SUCCESS
      OUTCOME Value modified by the program is NULL?: SUCCESS
    TESTCASE TEST_BOOLEAN F: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) FAILURE: Test failed at this level.
      OUTCOME Does the Boolean expression evaluate to TRUE?: SUCCESS
      OUTARG [Function return value]: SUCCESS
      OUTCOME Does the Boolean expression evaluate to TRUE?: FAILURE: Expression evaluates to NULL.
      OUTARG B2: SUCCESS
 UNITTEST TEST_CLOB: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
    TESTCASE TEST_CLOB S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
      OUTARG S: SUCCESS
      OUTARG [Function return value]: SUCCESS
      OUTCOME Scalar is equal to the expected value?: SUCCESS
      OUTCOME Scalar is equal to the expected value?: SUCCESS
    TESTCASE TEST_CLOB 32000: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
      OUTARG [Function return value]: SUCCESS
      OUTCOME Scalar is equal to the expected value?: SUCCESS
      OUTARG S: SUCCESS
      OUTCOME Scalar is equal to the expected value?: SUCCESS
HARNESS SCOTT.TEST_DATA_TYPES: 1 test case(s) failed
1

PL/SQL procedure successfully completed.

Such a naming convention would enable you to run all the unit and integration tests in a given test definition through a call like:

begin
  dbms_output.put_line(
    qcto_ci.run_test_definition(
      'SCOTT', 
      'TEST_DATA_TYPES', 
      'U%,I%'
    )
  );
end;
/